﻿/* License
 * 
 * The MIT License (MIT)
 *
 * Copyright (c) 2013, Sylvain PONTOREAU (pontoreau.sylvain@outlook.com / sylvain.pontoreau.com)
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in
 * all copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
 * THE SOFTWARE.
 */

using System.Globalization;
using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Linq;
using Xlsx.ComponentModel;
using Xlsx.ComponentModel.Enums;
using Xlsx.DataBinder.Extensions;

namespace Xlsx.DataBinder
{
    /// <summary>
    /// Helper for build xlsx file.
    /// </summary>
    internal static class XlsxBuilder
    {
        /// <summary>
        /// Create a workbook part
        /// </summary>
        /// <param name="document">Parent document</param>
        /// <returns>Workbook part</returns>
        internal static WorkbookPart CreateWorkbookPart(SpreadsheetDocument document)
        {
            var workbookPart = document.AddWorkbookPart();
            workbookPart.Workbook = new Workbook();
            workbookPart.Workbook.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            return workbookPart;
        }

        /// <summary>
        /// Create a worksheet
        /// </summary>
        /// <param name="workbookpart">Parent Worksheet part</param>
        /// <param name="document">Document</param>
        /// <param name="sheetId">Sheet id</param>
        /// <param name="sheetName">Sheet name</param>
        /// <returns>Worksheet</returns>
        internal static WorksheetPart CreateWorksheetPart(ref WorkbookPart workbookpart, SpreadsheetDocument document, UInt32 sheetId, string sheetName)
        {
            var sheets = workbookpart.Workbook.AppendChild(new Sheets());
            var worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
            var sheet = new Sheet
                {
                    Id = document.WorkbookPart.GetIdOfPart(worksheetPart), 
                    SheetId = sheetId, 
                    Name = sheetName
                };

            sheets.Append(sheet);

            var ws = new Worksheet();
            ws.AddNamespaceDeclaration("r", "http://schemas.openxmlformats.org/officeDocument/2006/relationships");
            var sheetData = new SheetData();
            ws.Append(sheetData);
            worksheetPart.Worksheet = ws;
            return worksheetPart;
        }
        
        /// <summary>
        /// Add header to a worksheet part
        /// </summary>
        /// <param name="wsp">Worksheet part</param>
        /// <param name="type">Object type</param>
        /// <returns>Next row index</returns>
        internal static uint AddHeader(ref WorksheetPart wsp, Type type)
        {
            uint rowNumber = 1;

            var properties = (from p in type.GetProperties() select p).ToList();
            properties = (from p in properties where p.GetXlsxBindableInfo() != null orderby p.GetXlsxBindableInfo().Order select p).ToList();

            var columnIndex = 0;
            foreach(var property in properties)
            {
                if(property.IsXlsxBindable())
                {
                    XlsxBindableInfo info = property.GetXlsxBindableInfo();

                    if(info.Type != XlsxDataType.None)
                    {
                        var columnName = XlsxHelper.GetColumnName(columnIndex);

                        Cell cell = InsertCellInWorksheet(columnName, rowNumber, wsp);
                        cell.CellValue = new CellValue(info.ColumnName);
                        cell.DataType = new EnumValue<CellValues>(CellValues.String);
                        cell.StyleIndex = 1U;
                        columnIndex++;
                    }
                }
            }

            return rowNumber++;
        }

        /// <summary>
        /// Insert a row
        /// </summary>
        /// <param name="wsp">Worksheet part</param>
        /// <param name="rowNumber">Row index</param>
        /// <param name="data">Data to insert</param>
        internal static void InsertRow(ref WorksheetPart wsp, uint rowNumber, object data)
        {
            Type type = data.GetType();
            var properties = (from p in type.GetProperties() select p).ToList();
            properties = (from p in properties where p.GetXlsxBindableInfo() != null orderby p.GetXlsxBindableInfo().Order select p).ToList();

            var columnIndex = 0;
            foreach (var property in properties)
            {
                if (property.IsXlsxBindable())
                {
                    XlsxBindableInfo info = property.GetXlsxBindableInfo();

                    var value = property.GetValue(data, null);

                    if (info.Type != XlsxDataType.None)
                    {
                        var columnName = XlsxHelper.GetColumnName(columnIndex);

                        Cell cell = InsertCellInWorksheet(columnName, rowNumber, wsp);
                        cell.CellValue = new CellValue(value == null ? string.Empty : value.ToString());
                        cell.DataType = new EnumValue<CellValues>(Convert(info.Type));
                        cell.StyleIndex = (rowNumber % 2 == 0) ? 2U : 3U;
                        columnIndex++;
                    }
                }
            }            
        }

        /// <summary>
        /// Convert XlsxDataType to CellValues
        /// </summary>
        /// <param name="type">DataType</param>
        /// <returns>CellValues</returns>
        internal static CellValues Convert(XlsxDataType type)
        {
            switch (type)
            {
                case XlsxDataType.Boolean :
                    return CellValues.String;
                case XlsxDataType.DateTime :
                    return CellValues.String;
                case XlsxDataType.Numeric :
                    return CellValues.Number;
                case XlsxDataType.Object :
                    return CellValues.String;
                case XlsxDataType.String :
                    return CellValues.String;
                default :
                    return CellValues.String;
            }
        }

        /// <summary>
        /// Convert XlsxBorderStyle to BorderStyleValues
        /// </summary>
        /// <param name="style">XlsxBorderStyle</param>
        /// <returns>BorderStyleValues</returns>
        internal static BorderStyleValues Convert(XlsxBorderStyle style)
        {
            switch (style)
            {
                case XlsxBorderStyle.Medium:
                    return BorderStyleValues.Medium;
                case XlsxBorderStyle.Simple:
                    return BorderStyleValues.Thin;
                default:
                    return BorderStyleValues.None;
            }
        }

        /// <summary>
        /// Insert cell
        /// </summary>
        /// <param name="columnName">Column name</param>
        /// <param name="rowIndex">Row index</param>
        /// <param name="worksheetPart">Worksheet Part</param>
        /// <returns>Cell insert</returns>
        internal static Cell InsertCellInWorksheet(string columnName, uint rowIndex, WorksheetPart worksheetPart)
        {
            var worksheet = worksheetPart.Worksheet;
            var sheetData = worksheet.GetFirstChild<SheetData>();
            var cellReference = columnName + rowIndex;

            // If the worksheet does not contain a row with the specified row index, insert one.
            Row row;
            if (sheetData.Elements<Row>().Count(r => r.RowIndex == rowIndex) != 0)
            {
                row = sheetData.Elements<Row>().First(r => r.RowIndex == rowIndex);
            }
            else
            {
                row = new Row { RowIndex = rowIndex };
                sheetData.Append(row);
            }

            // If there is not a cell with the specified column name, insert one.  
            if (row.Elements<Cell>().Any(c => c.CellReference.Value == columnName + rowIndex))
            {
                return row.Elements<Cell>().First(c => c.CellReference.Value == cellReference);
            }

            // Cells must be in sequential order according to CellReference. Determine where to insert the new cell.

            var myCellRefenceValue = columnName.Select((t, i) => ((int) t + ((i*26)))).Sum();

            var refCell = (from cell in row.Elements<Cell>() let cellColumnName = cell.CellReference.Value.Replace(rowIndex.ToString(CultureInfo.InvariantCulture), string.Empty) let currentCellReferenceValue = cellColumnName.Select((t, i) => ((int) t + ((i*26)))).Sum() where currentCellReferenceValue > myCellRefenceValue select cell).FirstOrDefault();

            var newCell = new Cell { CellReference = cellReference };
            row.InsertBefore(newCell, refCell);

            worksheet.Save();
            return newCell;
        }

        /// <summary>
        /// Apply styles to a workbook part
        /// </summary>
        /// <param name="workbookPart">WorkBook part</param>
        internal static void ApplyStyles(ref WorkbookStylesPart workbookPart, XlsxSkin headerSkin, XlsxSkin rowSkin, XlsxSkin alternateSkin)
        {
            var stylesheet = new Stylesheet();            

            #region Font
            var fonts = new Fonts();

            //header
            var fontHeader = new Font();
            
            if(headerSkin.IsBold)
                fontHeader.Bold = new Bold();

            if (headerSkin.IsItalic)
                fontHeader.Italic = new Italic();

            if (headerSkin.IsUnderline)
                fontHeader.Underline = new Underline();

            fontHeader.Color = new Color { Rgb = "FF" + headerSkin.HtmlFontColor.Replace("#", string.Empty) };
            fontHeader.FontSize = new FontSize { Val = headerSkin.FontSize };

            //row
            var fontRow = new Font();

            if (rowSkin.IsBold)
                fontRow.Bold = new Bold();

            if (rowSkin.IsItalic)
                fontRow.Italic = new Italic();

            if (rowSkin.IsUnderline)
                fontRow.Underline = new Underline();

            fontRow.Color = new Color { Rgb = "FF" + rowSkin.HtmlFontColor.Replace("#", string.Empty) };
            fontRow.FontSize = new FontSize { Val = rowSkin.FontSize };

            //alternate row
            var fontAlternateRow = new Font();

            if (alternateSkin.IsBold)
                fontAlternateRow.Bold = new Bold();

            if (alternateSkin.IsItalic)
                fontAlternateRow.Italic = new Italic();

            if (alternateSkin.IsUnderline)
                fontAlternateRow.Underline = new Underline();

            fontAlternateRow.Color = new Color { Rgb = "FF" + alternateSkin.HtmlFontColor.Replace("#", string.Empty) };
            fontAlternateRow.FontSize = new FontSize { Val = alternateSkin.FontSize };

            fonts.Append(new Font { Color = new Color { Theme = (UInt32Value)1U } }, fontHeader, fontRow, fontAlternateRow);//Default
            fonts.Count = UInt32Value.FromUInt32((uint)fonts.ChildElements.Count);
            #endregion

            #region Fill
            var fills = new Fills();

            //header fill
            var headerFill = new Fill();
            var headerPattern = new PatternFill { PatternType = PatternValues.Solid };
            headerPattern.Append(new ForegroundColor { Rgb = "FF" + headerSkin.HtmlFillColor.Replace("#", string.Empty) });
            headerFill.Append(headerPattern);
            
            //row fill
            var rowFill = new Fill();
            var rowPattern = new PatternFill { PatternType = PatternValues.Solid };
            rowPattern.Append(new ForegroundColor { Rgb = "FF" + rowSkin.HtmlFillColor.Replace("#", string.Empty) });
            rowFill.Append(rowPattern);

            //alternate fill
            var alternateFill = new Fill();
            var alternatePattern = new PatternFill { PatternType = PatternValues.Solid };
            alternatePattern.Append(new ForegroundColor { Rgb = alternateSkin.HtmlFillColor.Replace("#", string.Empty) });
            alternateFill.Append(alternatePattern);

            // Default value 1
            var fill1 = new Fill();
            fill1.Append(new PatternFill { PatternType = PatternValues.None });
            // Default value 2
            var fill2 = new Fill();
            fill2.Append(new PatternFill { PatternType = PatternValues.Gray125 });

            fills.Append(fill1, fill2, headerFill, rowFill, alternateFill);
            fills.Count = UInt32Value.FromUInt32((uint)fills.ChildElements.Count);
            #endregion

            #region Border
            var borders = new Borders();

            var border1 = new Border();
            var leftBorder1 = new LeftBorder();
            var rightBorder1 = new RightBorder();
            var topBorder1 = new TopBorder();
            var bottomBorder1 = new BottomBorder();
            var diagonalBorder1 = new DiagonalBorder();

            border1.Append(leftBorder1, rightBorder1, topBorder1, bottomBorder1, diagonalBorder1);

            var headerBorder = new Border();
            var leftHeaderBorder = headerSkin.HasBorder ? new LeftBorder { Style = Convert(headerSkin.BorderStyle) } : new LeftBorder();
            var rightHeaderBorder = headerSkin.HasBorder ? new RightBorder { Style = Convert(headerSkin.BorderStyle) } : new RightBorder();
            var topHeaderBorder = headerSkin.HasBorder ? new TopBorder { Style = Convert(headerSkin.BorderStyle) } : new TopBorder();
            var bottomHeaderBorder = headerSkin.HasBorder ? new BottomBorder { Style = Convert(headerSkin.BorderStyle) } : new BottomBorder();
            var diagonalHeaderBorder = new DiagonalBorder();

            headerBorder.Append(leftHeaderBorder, rightHeaderBorder, topHeaderBorder, bottomHeaderBorder, diagonalHeaderBorder);

            var rowBorder = new Border();
            var leftRowBorder = headerSkin.HasBorder ? new LeftBorder { Style = Convert(rowSkin.BorderStyle) } : new LeftBorder();
            var rightRowBorder = rowSkin.HasBorder ? new RightBorder { Style = Convert(rowSkin.BorderStyle) } : new RightBorder();
            var topRowBorder = rowSkin.HasBorder ? new TopBorder { Style = Convert(rowSkin.BorderStyle) } : new TopBorder();
            var bottomRowBorder = rowSkin.HasBorder ? new BottomBorder { Style = Convert(rowSkin.BorderStyle) } : new BottomBorder();
            var diagonalRowBorder = new DiagonalBorder();

            rowBorder.Append(leftRowBorder, rightRowBorder, topRowBorder, bottomRowBorder, diagonalRowBorder);

            var alternateBorder = new Border();
            var leftAlternateBorder = alternateSkin.HasBorder ? new LeftBorder { Style = Convert(alternateSkin.BorderStyle) } : new LeftBorder();
            var rightAlternateBorder = alternateSkin.HasBorder ? new RightBorder { Style = Convert(alternateSkin.BorderStyle) } : new RightBorder();
            var topAlternateBorder = alternateSkin.HasBorder ? new TopBorder { Style = Convert(alternateSkin.BorderStyle) } : new TopBorder();
            var bottomAlternateBorder = alternateSkin.HasBorder ? new BottomBorder { Style = Convert(alternateSkin.BorderStyle) } : new BottomBorder();
            var diagonalAlternateBorder = new DiagonalBorder();

            alternateBorder.Append(leftAlternateBorder, rightAlternateBorder, topAlternateBorder, bottomAlternateBorder, diagonalAlternateBorder);

            borders.Append(border1, headerBorder, rowBorder, alternateBorder);
            borders.Count = UInt32Value.FromUInt32((uint)borders.ChildElements.Count);
            #endregion


            var cellFormats = new CellFormats();

            var headerCellFormat = new CellFormat { FontId = 1U, FillId = 2U, BorderId = 1U, FormatId = 1U };
            var rowCellFormat = new CellFormat { FontId = 2U, FillId = 3U, BorderId = 2U, FormatId = 2U };
            var alternateCellFormat = new CellFormat { FontId = 3U, FillId = 4U, BorderId = 3U, FormatId = 3U };

            cellFormats.Append(new CellFormat { FontId = 0U, FillId = 0U, BorderId = 0U, FormatId = 0U }, headerCellFormat, rowCellFormat, alternateCellFormat);
            cellFormats.Count = UInt32Value.FromUInt32((uint)cellFormats.ChildElements.Count);

            stylesheet.Append(fonts, fills, borders, cellFormats);
            workbookPart.Stylesheet = stylesheet;
        }
    }
}
